<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
use App\Models\EmploymentContract;
use App\Models\Employee;

class RightController extends Controller
{
    public function getDepartmentStats()
    {
        $departmentStats = DB::table('wochat_departments')
            ->select('name')
            ->get();

        $aResult = [];
        foreach ($departmentStats as $department) {
           $aResult[] = [
            'name' => $department->name,
            'count' => DB::table('wochat_users')->where('department', $department->name)->count(),
           ];
        }

        return response()->json([
            'code' => 0,
            'msg' => '各部门人数统计数据获取成功',
            'data' => $aResult,
        ]);
    }

    // 统计合同在签署情况
    public function getContractStats()
    {
        $contractTypes = EmploymentContract::$typeMap;
        $now = Carbon::now();
        $expireDays = 30; // 30天内即将过期
        
        // 初始化表格数据
        $tableData = [];
        foreach ($contractTypes as $typeId => $typeName) {
            $tableData[] = [
                'contract_type' => $typeName,
                'signed' => 0,      // 已签（生效中）
                'unsigned' => 0,    // 未签
                'expiring' => 0,    // 即将过期
                'expired' => 0      // 已过期
            ];
        }
        
        // 1. 统计已签合同（生效中）
        $signedStats = DB::table('employment_contracts')
            ->select('type', DB::raw('COUNT(DISTINCT employee_id) as count'))
            ->where('status', 1) // 生效中
            ->whereNull('deleted_at')
            ->groupBy('type')
            ->get();
        
        // 2. 统计即将过期合同（生效中且30天内过期）
        $expiringStats = DB::table('employment_contracts')
            ->select('type', DB::raw('COUNT(DISTINCT employee_id) as count'))
            ->where('status', 1) // 生效中
            ->whereNull('deleted_at')
            ->where('expiration_at', '>', $now->format('Y-m-d'))
            ->where('expiration_at', '<=', $now->copy()->addDays($expireDays)->format('Y-m-d'))
            ->groupBy('type')
            ->get();
        
        // 3. 统计已过期合同
        $expiredStats = DB::table('employment_contracts')
            ->select('type', DB::raw('COUNT(DISTINCT employee_id) as count'))
            ->where('status', 2) // 已到期
            ->whereNull('deleted_at')
            ->groupBy('type')
            ->get();
        
        // 4. 统计未签合同的员工（有入职档案但没有合同记录）
        $unsignedEmployees = DB::table('employees')
            ->leftJoin('enrollments', 'employees.id', '=', 'enrollments.employee_id')
            ->leftJoin('employment_contracts', 'employees.id', '=', 'employment_contracts.employee_id')
            ->select('enrollments.type as enrollment_type', DB::raw('COUNT(DISTINCT employees.id) as count'))
            ->whereNull('employees.deleted_at')
            ->whereNull('employees.separated_at') // 在职员工
            ->whereNotNull('enrollments.id')
            ->where('enrollments.status', 4) // 入职档案生效中
            ->whereNull('employment_contracts.id') // 没有合同记录
            ->groupBy('enrollments.type')
            ->get();
        
        // 填充表格数据
        foreach ($tableData as $index => $row) {
            $typeId = array_search($row['contract_type'], $contractTypes);
            
            // 填充已签数据
            $signedCount = $signedStats->where('type', $typeId)->first();
            if ($signedCount) {
                $tableData[$index]['signed'] = $signedCount->count;
            }
            
            // 填充即将过期数据
            $expiringCount = $expiringStats->where('type', $typeId)->first();
            if ($expiringCount) {
                $tableData[$index]['expiring'] = $expiringCount->count;
            }
            
            // 填充已过期数据
            $expiredCount = $expiredStats->where('type', $typeId)->first();
            if ($expiredCount) {
                $tableData[$index]['expired'] = $expiredCount->count;
            }
            
            // 对于未签的员工，根据入职档案类型映射到合同类型
            foreach ($unsignedEmployees as $unsigned) {
                $contractType = $this->mapEnrollmentTypeToContractType($unsigned->enrollment_type);
                if ($contractType == $typeId) {
                    $tableData[$index]['unsigned'] += $unsigned->count;
                }
            }
        }
        
        // 格式化数据，将0显示为"--"
        foreach ($tableData as $index => $row) {
            $tableData[$index]['signed'] = $row['signed'] > 0 ? $row['signed'] : '--';
            $tableData[$index]['unsigned'] = $row['unsigned'] > 0 ? $row['unsigned'] : '--';
            $tableData[$index]['expiring'] = $row['expiring'] > 0 ? $row['expiring'] : '--';
            $tableData[$index]['expired'] = $row['expired'] > 0 ? $row['expired'] : '--';
        }
        
        return response()->json([
            'code' => 0,
            'msg' => '合同统计数据获取成功',
            'data' => [
                'table_data' => $tableData,
                'contract_types' => $contractTypes
            ]
        ]);
    }
    
    /**
     * 将入职档案类型映射到合同类型
     * 入职档案类型：1全职 2兼职 3实习 4外包 5劳务 6顾问 7返聘
     * 合同类型：1固定期限 2无固定期限 3实习协议 4劳务派遣 5非全日制
     */
    private function mapEnrollmentTypeToContractType($enrollmentType)
    {
        $mapping = [
            1 => 1, // 全职 -> 固定期限
            2 => 5, // 兼职 -> 非全日制
            3 => 3, // 实习 -> 实习协议
            4 => 1, // 外包 -> 固定期限
            5 => 4, // 劳务 -> 劳务派遣
            6 => 1, // 顾问 -> 固定期限
            7 => 1, // 返聘 -> 固定期限
        ];
        
        return $mapping[$enrollmentType] ?? 1; // 默认为固定期限
    }

    // 员工考勤情况
    public function getAttendanceStats(Request $request)
    {
        $page = $request->get('page', 1);
        $perPage = $request->get('per_page', 4);
        $period = $request->get('period', 'last_month'); // last_month: 上月, year: 年度, month: 指定月份
        $year = $request->get('year');
        $month = $request->get('month');
        $currentDate = Carbon::now();
        
        // 根据参数确定统计时间范围和条件
        if ($period === 'year') {
            // 年度统计
            $targetYear = $year ? $year : $currentDate->year;
            $periodCondition = "YEAR(cw.period_at) = {$targetYear}";
            $periodLabel = $targetYear . '年度';
        } elseif ($period === 'month' && $year && $month) {
            // 指定月份统计
            $targetDate = Carbon::createFromDate($year, $month, 1);
            $periodCondition = "YEAR(cw.period_at) = {$year} AND MONTH(cw.period_at) = {$month}";
            $periodLabel = $targetDate->format('Y年m月');
        } else {
            // 上月统计（默认）
            $lastMonth = $currentDate->copy()->subMonth();
            $targetYear = $lastMonth->year;
            $targetMonth = $lastMonth->month;
            $periodCondition = "YEAR(cw.period_at) = {$targetYear} AND MONTH(cw.period_at) = {$targetMonth}";
            $periodLabel = $lastMonth->format('Y年m月');
        }
        
        // 构建查询 - 关联员工信息
        $query = DB::table('checkin_works as cw')
            ->select(
                'cw.employee_id',
                'e.name as employee_name',
                DB::raw('SUM(cw.work_days) as total_work_days'),
                DB::raw('SUM(CAST(cw.actual_days as SIGNED)) as total_actual_days'), // 将char转为数字
                DB::raw('CASE 
                    WHEN SUM(cw.work_days) > 0 THEN 
                        ROUND((SUM(CAST(cw.actual_days as SIGNED)) / SUM(cw.work_days)) * 100, 2)
                    ELSE 0 
                END as attendance_rate')
            )
            ->leftJoin('employees as e', 'cw.employee_id', '=', 'e.id')
            ->whereRaw($periodCondition)
            ->whereNull('cw.deleted_at')
            ->whereNull('e.deleted_at')
            ->groupBy('cw.employee_id', 'e.name')
            ->orderBy('attendance_rate', 'desc');
        
        // 获取总数 - 移除排序避免计算字段错误
        $totalQuery = DB::table('checkin_works as cw')
            ->leftJoin('employees as e', 'cw.employee_id', '=', 'e.id')
            ->whereRaw($periodCondition)
            ->whereNull('cw.deleted_at')
            ->whereNull('e.deleted_at')
            ->groupBy('cw.employee_id', 'e.name');
        $total = $totalQuery->count();
        
        // 分页获取数据
        $attendanceData = $query->skip(($page - 1) * $perPage)
                               ->take($perPage)
                               ->get();
        
        // 格式化结果
        $result = [];
        foreach ($attendanceData as $data) {
            $result[] = [
                'employee_id' => $data->employee_id,
                'employee_name' => $data->employee_name ?? '未知员工',
                'total_work_days' => $data->total_work_days,
                'total_actual_days' => $data->total_actual_days,
                'attendance_rate' => $data->attendance_rate . '%',
            ];
        }
        
        // 计算总体出勤率
        $overallStats = DB::table('checkin_works as cw')
            ->select(
                DB::raw('COUNT(DISTINCT cw.employee_id) as total_employees'),
                DB::raw('CASE 
                    WHEN SUM(cw.work_days) > 0 THEN 
                        ROUND((SUM(CAST(cw.actual_days as SIGNED)) / SUM(cw.work_days)) * 100, 2)
                    ELSE 0 
                END as overall_attendance_rate')
            )
            ->leftJoin('employees as e', 'cw.employee_id', '=', 'e.id')
            ->whereRaw($periodCondition)
            ->whereNull('cw.deleted_at')
            ->whereNull('e.deleted_at')
            ->first();
        
        // 计算分页信息
        $totalPages = ceil($total / $perPage);
        $hasNextPage = $page < $totalPages;
        $hasPrevPage = $page > 1;
        
        return response()->json([
            'code' => 0,
            'msg' => '员工考勤统计数据获取成功',
            'data' => [
                'attendance_list' => $result,
                'pagination' => [
                    'current_page' => $page,
                    'per_page' => $perPage,
                    'total' => $total,
                    'total_pages' => $totalPages,
                    'has_next_page' => $hasNextPage,
                    'has_prev_page' => $hasPrevPage
                ]
            ]
        ]);
    }

    // 人员行为识别预警
    public function getBehaviorStats(Request $request)
    {
        $page = $request->get('page', 1);
        $perPage = 4; // 每页展示4条数据
        $period = $request->get('period', 'last_month'); // last_month: 上月, year: 年度
        $currentDate = Carbon::now();
        
        // 根据参数确定统计时间范围
        if ($period === 'year') {
            // 年度统计
            $targetYear = $currentDate->year;
            $periodCondition = "YEAR(rpr.created_at) = {$targetYear}";
            $periodLabel = $targetYear . '年度';
        } else {
            // 上月统计（默认）
            $lastMonth = $currentDate->copy()->subMonth();
            $targetYear = $lastMonth->year;
            $targetMonth = $lastMonth->month;
            $periodCondition = "YEAR(rpr.created_at) = {$targetYear} AND MONTH(rpr.created_at) = {$targetMonth}";
            $periodLabel = $lastMonth->format('Y年m月');
        }
        
        // 1. 获取员工违规记录统计（根据reward_punishment_type_id去重，获取最新记录）
        $behaviorQuery = DB::table('reward_punishment_records as rpr')
            ->select(
                'rpr.employee_id',
                'e.name as employee_name',
                'rpr.level',
                'rpt.name as violation_type',
                'rpr.reward_punishment_type_id',
                'rpr.created_at'
            )
            ->leftJoin('employees as e', 'rpr.employee_id', '=', 'e.id')
            ->leftJoin('reward_punishment_types as rpt', 'rpr.reward_punishment_type_id', '=', 'rpt.id')
            ->where('rpr.status', 4)
            ->whereRaw($periodCondition)
            ->whereNull('rpr.deleted_at')
            ->whereNull('e.deleted_at')
            ->whereNull('e.separated_at')
            ->whereNull('rpt.deleted_at');
        
        // 2. 获取所有数据并处理去重
        $allData = $behaviorQuery
            ->orderBy('rpr.created_at', 'desc')
            ->get();
        
        // 3. 按employee_id和reward_punishment_type_id去重，保留最新记录
        $uniqueData = $allData->groupBy(function($item) {
            return $item->employee_id . '_' . $item->reward_punishment_type_id;
        })->map(function($group) {
            return $group->first(); // 取每组的第一个（最新的）记录
        })->values();
        
        // 4. 按level降序排序
        $sortedData = $uniqueData->sortByDesc('level')->values();
        
        // 5. 计算总数和分页
        $total = $sortedData->count();
        $paginatedData = $sortedData->slice(($page - 1) * $perPage, $perPage);
        
        // 6. 格式化结果
        $result = [];
        // 等级描述映射
        $levelDescriptions = [
            0 => '轻微',
            1 => '一般',
            2 => '严重'
        ];
        foreach ($paginatedData as $data) {
            $result[] = [
                'employee_name' => $data->employee_name ?? '未知员工',
                'level' => (int)$data->level,
                'level_description' => $levelDescriptions[$data->level] ?? '未知',
                'violation_type' => $data->violation_type ?? '未知类型'
            ];
        }
        
        // 7. 计算总体统计
        $overallStats = (object)[
            'total_employees' => $uniqueData->unique('employee_id')->count(),
            'total_minor' => $uniqueData->where('level', 0)->count(),
            'total_general' => $uniqueData->where('level', 1)->count(),
            'total_serious' => $uniqueData->where('level', 2)->count(),
            'total_violations' => $uniqueData->count()
        ];
        
        // 8. 计算分页信息
        $totalPages = ceil($total / $perPage);
        $hasNextPage = $page < $totalPages;
        $hasPrevPage = $page > 1;
        
        return response()->json([
            'code' => 0,
            'msg' => '人员行为识别预警数据获取成功',
            'data' => [
                'behavior_list' => $result,
                'overall_stats' => [
                    'total_employees' => $overallStats->total_employees ?? 0,
                    'total_minor' => $overallStats->total_minor ?? 0,
                    'total_general' => $overallStats->total_general ?? 0,
                    'total_serious' => $overallStats->total_serious ?? 0,
                    'total_violations' => $overallStats->total_violations ?? 0,
                ],
                'pagination' => [
                    'current_page' => $page,
                    'per_page' => $perPage,
                    'total' => $total,
                    'total_pages' => $totalPages,
                    'has_next_page' => $hasNextPage,
                    'has_prev_page' => $hasPrevPage
                ]
            ]
        ]);
    }

    // 2025年度薪资总体情况统计
    public function getSalaryStats(Request $request)
    {
        $period = $request->get('period', 'current_year'); // current_year: 当前年份, last_month: 上月, last_quarter: 上个季度
        $currentDate = Carbon::now();
        
        // 根据参数确定统计时间范围
        $periodCondition = '';
        $periodLabel = '';
        
        if ($period === 'last_month') {
            // 上月统计
            $lastMonth = $currentDate->copy()->subMonth();
            $targetYear = $lastMonth->year;
            $targetMonth = $lastMonth->month;
            $periodCondition = "YEAR(s.period_at) = {$targetYear} AND MONTH(s.period_at) = {$targetMonth}";
            $periodLabel = $lastMonth->format('Y年m月');
        } elseif ($period === 'last_quarter') {
            // 上个季度统计
            $lastQuarter = $currentDate->copy()->subQuarter();
            $targetYear = $lastQuarter->year;
            $targetQuarter = $lastQuarter->quarter;
            $startMonth = ($targetQuarter - 1) * 3 + 1;
            $endMonth = $targetQuarter * 3;
            $periodCondition = "YEAR(s.period_at) = {$targetYear} AND MONTH(s.period_at) BETWEEN {$startMonth} AND {$endMonth}";
            $periodLabel = $targetYear . '年第' . $targetQuarter . '季度';
        } else {
            // 当前年份统计（默认）
            $targetYear = $currentDate->year;
            $periodCondition = "YEAR(s.period_at) = {$targetYear}";
            $periodLabel = $targetYear . '年度';
        }
        
        // 字段映射
        $fieldMapping = [
            1 => '基本工资',
            2 => '加班',
            6 => '奖金',
            5 => '绩效',
            'social_security' => '社保补贴', // 3, 8
            'other' => '其他'
        ];
        
        // 社保补贴的field_id
        $socialSecurityIds = [3, 8];
        
        // 获取薪资统计数据 - 关联 salaries 表按结算月份筛选
        $salaryStats = DB::table('salary_details as sd')
            ->select('sd.salary_field_id', DB::raw('SUM(sd.amount) as total_amount'))
            ->leftJoin('salaries as s', 'sd.salary_id', '=', 's.id')
            ->whereNull('sd.deleted_at')
            ->whereNull('s.deleted_at')
            ->whereRaw($periodCondition)
            ->groupBy('sd.salary_field_id')
            ->get();
        
        // 初始化结果数组
        $result = [
            'basic_salary' => 0,      // 基本工资
            'overtime' => 0,          // 加班
            'bonus' => 0,             // 奖金
            'performance' => 0,       // 绩效
            'social_security' => 0,   // 社保补贴
            'other' => 0              // 其他
        ];
        
        // 处理统计数据
        foreach ($salaryStats as $stat) {
            $fieldId = $stat->salary_field_id;
            $amount = $stat->total_amount;
            
            switch ($fieldId) {
                case 1:
                    $result['basic_salary'] += $amount;
                    break;
                case 2:
                    $result['overtime'] += $amount;
                    break;
                case 6:
                    $result['bonus'] += $amount;
                    break;
                case 5:
                    $result['performance'] += $amount;
                    break;
                case 3:
                case 8:
                    $result['social_security'] += $amount;
                    break;
                default:
                    $result['other'] += $amount;
                    break;
            }
        }
        
        // 格式化返回数据
        $formattedResult = [
            [
                'type' => '基本工资',
                'amount' => number_format($result['basic_salary'] / 100, 2),
                'amount_formatted' => number_format($result['basic_salary'] / 100, 2) . '元'
            ],
            [
                'type' => '加班',
                'amount' => number_format($result['overtime'] / 100, 2),
                'amount_formatted' => number_format($result['overtime'] / 100, 2) . '元'
            ],
            [
                'type' => '奖金',
                'amount' => number_format($result['bonus'] / 100, 2),
                'amount_formatted' => number_format($result['bonus'] / 100, 2) . '元'
            ],
            [
                'type' => '绩效',
                'amount' => number_format($result['performance'] / 100, 2),
                'amount_formatted' => number_format($result['performance'] / 100, 2) . '元'
            ],
            [
                'type' => '社保补贴',
                'amount' => number_format($result['social_security'] / 100, 2),
                'amount_formatted' => number_format($result['social_security'] / 100, 2) . '元'
            ],
            [
                'type' => '其他',
                'amount' => number_format($result['other'] / 100, 2),
                'amount_formatted' => number_format($result['other'] / 100, 2) . '元'
            ]
        ];
        
        // 计算总薪资
        $totalSalary = number_format(array_sum($result) / 100, 2);
        
        return response()->json([
            'code' => 0,
            'msg' => '薪资统计数据获取成功',
            'data' => [
                'period' => $period,
                'period_label' => $periodLabel,
                'salary_stats' => $formattedResult,
                'total_salary' => $totalSalary,
                'total_salary_formatted' => $totalSalary . '元'
            ]
        ]);
    }

    // 各部门考勤情况
    public function getDepartmentAttendanceStats(Request $request)
    {
        $page = $request->get('page', 1);
        $perPage = $request->get('per_page', 5); // 每页展示5条数据
        $period = $request->get('period', 'last_month'); // last_month: 上月, year: 年度
        $currentDate = Carbon::now();
        
        // 根据参数确定统计时间范围
        if ($period === 'year') {
            // 年度统计
            $targetYear = $currentDate->year;
            $periodCondition = "YEAR(cw.period_at) = {$targetYear}";
            $periodLabel = $targetYear . '年度';
        } else {
            // 上月统计（默认）
            $lastMonth = $currentDate->copy()->subMonth();
            $targetYear = $lastMonth->year;
            $targetMonth = $lastMonth->month;
            $periodCondition = "YEAR(cw.period_at) = {$targetYear} AND MONTH(cw.period_at) = {$targetMonth}";
            $periodLabel = $lastMonth->format('Y年m月');
        }
        
        // 构建查询 - 按部门统计出勤率
        $query = DB::table('wochat_departments as d')
            ->select(
                'd.name as department_name',
                DB::raw('COUNT(DISTINCT wu.employee_id) as total_employees'),
                DB::raw('CASE 
                    WHEN SUM(cw.work_days) > 0 THEN 
                        ROUND((SUM(CAST(cw.actual_days as SIGNED)) / SUM(cw.work_days)) * 100, 2)
                    ELSE 0 
                END as attendance_rate'),
                DB::raw('SUM(cw.work_days) as total_work_days'),
                DB::raw('SUM(CAST(cw.actual_days as SIGNED)) as total_actual_days')
            )
            ->leftJoin('wochat_users as wu', 'd.name', '=', 'wu.department')
            ->leftJoin('checkin_works as cw', function($join) use ($periodCondition) {
                $join->on('wu.employee_id', '=', 'cw.employee_id')
                     ->whereRaw($periodCondition)
                     ->whereNull('cw.deleted_at');
            })
            ->whereNull('d.deleted_at')
            ->whereNull('wu.deleted_at')
            ->where('wu.employee_id', '>', 0) // 确保有关联的员工
            ->groupBy('d.name')
            ->orderBy('attendance_rate', 'desc');
        
        // 获取总数 - 计算有考勤数据的部门数量
        $totalQuery = DB::table('wochat_departments as d')
            ->leftJoin('wochat_users as wu', 'd.name', '=', 'wu.department')
            ->leftJoin('checkin_works as cw', function($join) use ($periodCondition) {
                $join->on('wu.employee_id', '=', 'cw.employee_id')
                     ->whereRaw($periodCondition)
                     ->whereNull('cw.deleted_at');
            })
            ->whereNull('d.deleted_at')
            ->whereNull('wu.deleted_at')
            ->where('wu.employee_id', '>', 0)
            ->groupBy('d.name')
            ->havingRaw('COUNT(DISTINCT wu.employee_id) > 0');
        
    
        $total = $totalQuery->count();
        
        // 分页获取数据
        $attendanceData = $query->skip(($page - 1) * $perPage)
                               ->take($perPage)
                               ->get();
        
        // 格式化结果
        $result = [];
        foreach ($attendanceData as $data) {
            $result[] = [
                'department_name' => $data->department_name,
                'total_employees' => $data->total_employees,
                'attendance_rate' => $data->attendance_rate . '%',
                'total_work_days' => $data->total_work_days,
                'total_actual_days' => $data->total_actual_days,
                'attendance_rate_value' => $data->attendance_rate // 用于排序的数值
            ];
        }
        
        // 计算全公司总体出勤率
        $overallStats = DB::table('wochat_users as wu')
            ->select(
                DB::raw('COUNT(DISTINCT wu.employee_id) as total_employees'),
                DB::raw('COUNT(DISTINCT wu.department) as total_departments'),
                DB::raw('CASE 
                    WHEN SUM(cw.work_days) > 0 THEN 
                        ROUND((SUM(CAST(cw.actual_days as SIGNED)) / SUM(cw.work_days)) * 100, 2)
                    ELSE 0 
                END as overall_attendance_rate'),
                DB::raw('SUM(cw.work_days) as total_work_days'),
                DB::raw('SUM(CAST(cw.actual_days as SIGNED)) as total_actual_days')
            )
            ->leftJoin('checkin_works as cw', function($join) use ($periodCondition) {
                $join->on('wu.employee_id', '=', 'cw.employee_id')
                     ->whereRaw($periodCondition)
                     ->whereNull('cw.deleted_at');
            })
            ->whereNull('wu.deleted_at')
            ->where('wu.employee_id', '>', 0)
            ->first();
        
        // 计算分页信息
        $totalPages = ceil($total / $perPage);
        $hasNextPage = $page < $totalPages;
        $hasPrevPage = $page > 1;
        
        return response()->json([
            'code' => 0,
            'msg' => '各部门考勤统计数据获取成功',
            'data' => [
                'period_label' => $periodLabel,
                'department_attendance_list' => $result,
                'overall_stats' => [
                    'total_employees' => $overallStats->total_employees ?? 0,
                    'total_departments' => $overallStats->total_departments ?? 0,
                    'overall_attendance_rate' => ($overallStats->overall_attendance_rate ?? 0) . '%',
                    'total_work_days' => $overallStats->total_work_days ?? 0,
                    'total_actual_days' => $overallStats->total_actual_days ?? 0
                ],
                'pagination' => [
                    'current_page' => $page,
                    'per_page' => $perPage,
                    'total' => $total,
                    'total_pages' => $totalPages,
                    'has_next_page' => $hasNextPage,
                    'has_prev_page' => $hasPrevPage
                ]
            ]
        ]);
    }

    // 公司各部门人数
    public function getCompanyDepartmentStats()
    {
        // 统计各部门人数
        $departmentStats = DB::table('wochat_departments as d')
            ->select(
                'd.name as department_name',
                DB::raw('COUNT(wu.id) as employee_count')
            )
            ->leftJoin('wochat_users as wu', 'd.name', '=', 'wu.department')
            ->whereNull('d.deleted_at')
            ->whereNull('wu.deleted_at')
            ->where('wu.employee_id', '>', 0) // 确保有关联的员工
            ->groupBy('d.name')
            ->orderBy('employee_count', 'desc')
            ->get();
        
        // 格式化结果
        $result = [];
        foreach ($departmentStats as $department) {
            $result[] = [
                'department_name' => $department->department_name,
                'employee_count' => $department->employee_count
            ];
        }
        
        return response()->json([
            'code' => 0,
            'msg' => '公司各部门人数统计获取成功',
            'data' => $result
        ]);
    }

    // 违规行为排名
    public function getViolationRank(Request $request)
    {
        $period = $request->get('period', 'last_month'); // last_month: 上月, year: 年度
        $year = $request->get('year');
        $month = $request->get('month');
        $currentDate = Carbon::now();
        
        // 根据参数确定统计时间范围和条件
        $periodCondition = '';
        $periodLabel = '所有时间';
        
        if ($period === 'year') {
            // 年度统计
            $targetYear = $year ? $year : $currentDate->year;
            $periodCondition = "YEAR(rpr.created_at) = {$targetYear}";
            $periodLabel = $targetYear . '年度';
        }  elseif ($period === 'last_month') {
            // 上月统计
            $lastMonth = $currentDate->copy()->subMonth();
            $targetYear = $lastMonth->year;
            $targetMonth = $lastMonth->month;
            $periodCondition = "YEAR(rpr.created_at) = {$targetYear} AND MONTH(rpr.created_at) = {$targetMonth}";
            $periodLabel = $lastMonth->format('Y年m月');
        }
        
        // 获取所有违规类型（惩罚类型）
        $violationTypes = DB::table('reward_punishment_types as rpt')
            ->select('id', 'name')
            ->where('is_active', 1) // 启用状态
            ->whereNull('deleted_at')
            ->pluck('name', 'id')
            ->toArray();
        
        // 构建基础查询
        $violationQuery = DB::table('reward_punishment_records as rpr')
            ->select(
                'rpr.employee_id',
                'e.name as employee_name',
                'rpr.reward_punishment_type_id',
                'rpt.name as violation_type',
                DB::raw('COUNT(*) as violation_count')
            )
            ->leftJoin('employees as e', 'rpr.employee_id', '=', 'e.id')
            ->leftJoin('reward_punishment_types as rpt', 'rpr.reward_punishment_type_id', '=', 'rpt.id')
            ->where('rpr.status', 4) // 通过状态
            ->whereNull('rpr.deleted_at')
            ->whereNull('e.deleted_at')
            ->whereNull('e.separated_at') // 在职员工
            ->whereNull('rpt.deleted_at');
        
        // 应用时间条件
        if ($periodCondition) {
            $violationQuery->whereRaw($periodCondition);
        }
        
        // 获取员工违规记录统计
        $violationRecords = $violationQuery
            ->groupBy('rpr.employee_id', 'e.name', 'rpr.reward_punishment_type_id', 'rpt.name')
            ->get();
        
        // 构建员工违规数据结构
        $employeeViolationsData = [];
        foreach ($violationRecords as $record) {
            $employeeId = $record->employee_id;
            $employeeName = $record->employee_name;
            $violationType = $record->violation_type;
            $violationCount = $record->violation_count;
            
            if (!isset($employeeViolationsData[$employeeId])) {
                $employeeViolationsData[$employeeId] = [
                    'employee_name' => $employeeName,
                    'violations' => []
                ];
            }
            
            $employeeViolationsData[$employeeId]['violations'][$violationType] = $violationCount;
        }
        
        // 格式化员工违规数据
        $employeeViolations = [];
        foreach ($employeeViolationsData as $employeeData) {
            $totalViolations = array_sum($employeeData['violations']);
            
            if ($totalViolations > 0) { // 只显示有违规记录的员工
                $violationCounts = [];
                
                // 为每个违规类型设置计数，如果没有记录则为0
                foreach ($violationTypes as $typeId => $typeName) {
                    $violationCounts[$typeName] = $employeeData['violations'][$typeName] ?? 0;
                }
                
                $employeeViolations[] = [
                    'employee_name' => $employeeData['employee_name'],
                    'total_violations' => $totalViolations,
                    'violation_details' => $violationCounts
                ];
            }
        }
        
        // 按总违规次数降序排列
        usort($employeeViolations, function($a, $b) {
            return $b['total_violations'] - $a['total_violations'];
        });
        
        return response()->json([
            'code' => 0,
            'msg' => '违规行为排名数据获取成功',
            'data' => [
                'period' => $period,
                'period_label' => $periodLabel,
                'violation_types' => $violationTypes,
                'employee_violations' => $employeeViolations
            ]
        ]);
    }

    // 岗位层级分类
    public function getPositionLevelStats()
    {
        // Mock数据 - 岗位层级分类
        $positionLevels = [
            [
                'level_id' => 1,
                'level_name' => '高级管理层',
                "num" => 10
            ],
            [
                'level_id' => 2,
                'level_name' => '中级管理层',
                "num" => 20
            ],
            [
                'level_id' => 3,
                'level_name' => '基层管理层',
                "num" => 30
            ],
            [
                'level_id' => 4,
                'level_name' => '专业人员',
                "num" => 40
            ],
            [
                'level_id' => 5,
                'level_name' => '一般人员',
                "num" => 50
            ]
        ];
        
       
        return response()->json([
            'code' => 0,
            'msg' => '岗位层级分类数据获取成功',
            'data' => $positionLevels
        ]);
    }

    // 岗位层级类型平均月薪
    public function getPositionLevelSalaryStats()
    {
        // Mock数据 - 岗位层级薪资统计
        $positionLevelSalaryData = [
            [
                'level_id' => 1,
                'level_name' => '高级管理层',
                'average_salary' => 100000, // 10万
                'employee_count' => 6,
                'salary_range' => '8万-12万',
                'description' => '公司高级管理人员'
            ],
            [
                'level_id' => 2,
                'level_name' => '中级管理层', 
                'average_salary' => 80000, // 8万
                'employee_count' => 15,
                'salary_range' => '6万-10万',
                'description' => '部门和项目负责人'
            ],
            [
                'level_id' => 3,
                'level_name' => '基层管理层',
                'average_salary' => 60000, // 6万
                'employee_count' => 28,
                'salary_range' => '4万-8万',
                'description' => '基层管理人员'
            ],
            [
                'level_id' => 4,
                'level_name' => '专业人员',
                'average_salary' => 90000, // 9万
                'employee_count' => 45,
                'salary_range' => '6万-12万',
                'description' => '专业技能骨干'
            ],
            [
                'level_id' => 5,
                'level_name' => '一般人员',
                'average_salary' => 40000, // 4万
                'employee_count' => 78,
                'salary_range' => '3万-6万',
                'description' => '基础岗位员工'
            ]
        ];
        
        // 薪资段统计 - 按平均月薪分组统计人数
        $salaryRangeStats = [
            [
                'salary_level' => 40000,
                'salary_formatted' => '4万',
                'employee_count' => 78, // 一般人员
                'levels' => ['一般人员']
            ],
            [
                'salary_level' => 60000,
                'salary_formatted' => '6万',
                'employee_count' => 28, // 基层管理层
                'levels' => ['基层管理层']
            ],
            [
                'salary_level' => 80000,
                'salary_formatted' => '8万',
                'employee_count' => 15, // 中级管理层
                'levels' => ['中级管理层']
            ],
            [
                'salary_level' => 90000,
                'salary_formatted' => '9万',
                'employee_count' => 45, // 专业人员
                'levels' => ['专业人员']
            ],
            [
                'salary_level' => 100000,
                'salary_formatted' => '10万',
                'employee_count' => 6, // 高级管理层
                'levels' => ['高级管理层']
            ]
        ];
        
        // 计算总人数和平均薪资
        $totalEmployees = array_sum(array_column($positionLevelSalaryData, 'employee_count'));
        $totalSalarySum = 0;
        
        foreach ($positionLevelSalaryData as $level) {
            $totalSalarySum += $level['average_salary'] * $level['employee_count'];
        }
        
        $overallAverageSalary = $totalEmployees > 0 ? round($totalSalarySum / $totalEmployees, 2) : 0;
        
        // 为每个层级添加占比信息
        foreach ($positionLevelSalaryData as &$level) {
            $level['percentage'] = $totalEmployees > 0 ? round(($level['employee_count'] / $totalEmployees) * 100, 2) : 0;
            $level['percentage_formatted'] = $level['percentage'] . '%';
            $level['average_salary_formatted'] = number_format($level['average_salary'] / 10000, 1) . '万';
        }
        
        return response()->json([
            'code' => 0,
            'msg' => '岗位层级薪资统计数据获取成功',
            'data' => $salaryRangeStats
        ]);
    }

    // 五险一金购买情况
    public function getInsuranceStats()
    {
        // 统计各类保险购买情况
        $insuranceStats = DB::table('insurance_funds as if')
            ->join('insurance_temps as it', 'if.insurance_temp_id', '=', 'it.id')
            ->join('employees as e', 'e.id', '=', 'if.employee_id')
            ->selectRaw('
                it.name,
                it.type,
                COUNT(if.insurance_temp_id) AS total_count,
                COUNT(CASE WHEN if.expired_at IS NULL OR if.expired_at > CURDATE() THEN 1 END) AS active_count,
                COUNT(CASE WHEN if.expired_at IS NOT NULL AND DATEDIFF(if.expired_at, CURDATE()) BETWEEN 0 AND 29 THEN 1 END) AS expire_count,
                COUNT(CASE WHEN if.expired_at IS NOT NULL AND if.expired_at < CURDATE() THEN 1 END) AS expired_count
            ')
            ->whereNull('if.deleted_at')
            ->whereNull('it.deleted_at')
            ->whereNull('e.deleted_at')
            ->whereNull('e.separated_at') // 只统计在职员工
            ->groupBy('if.insurance_temp_id', 'it.name', 'it.type')
            ->orderBy('it.type')
            ->get();
        
        // 格式化结果数据
        $result = [];
        foreach ($insuranceStats as $insurance) {
            $result[] = [
                'name' => $insurance->name,
                'type' => $insurance->type,
                'total_count' => $insurance->total_count,          // 实际购买人数
                'active_count' => $insurance->active_count,        // 参保人数（有效的）
                'expire_count' => $insurance->expire_count,        // 即将到期数量
                'expired_count' => $insurance->expired_count,      // 已到期数量
                'effective_rate' => $insurance->total_count > 0 ? 
                    round(($insurance->active_count / $insurance->total_count) * 100, 2) : 0
            ];
        }
        
        // 计算总体统计
        $totalStats = [
            'total_employees_with_insurance' => DB::table('insurance_funds as if')
                ->join('employees as e', 'e.id', '=', 'if.employee_id')
                ->whereNull('if.deleted_at')
                ->whereNull('e.deleted_at')
                ->whereNull('e.separated_at')
                ->distinct('if.employee_id')
                ->count(),
            'total_insurance_records' => array_sum(array_column($result, 'total_count')),
            'total_active_records' => array_sum(array_column($result, 'active_count')),
            'total_expire_records' => array_sum(array_column($result, 'expire_count')),
            'total_expired_records' => array_sum(array_column($result, 'expired_count'))
        ];
        
        return response()->json([
            'code' => 0,
            'msg' => '五险一金购买情况统计获取成功',
            'data' => [
                'insurance_stats' => $result,
                'total_stats' => $totalStats
            ]
        ]);
    }

    // 规章制度展示
    public function getRegulationsStats()
    {
        // Mock数据 - 规章制度文件
        $regulationsData = [
            [
                'id' => 1,
                'file_name' => '员工手册',
                'status_text' => '生效中',
            ],
            [
                'id' => 2,
                'file_name' => '考勤管理制度',
                'status_text' => '生效中',
            ],
            [
                'id' => 3,
                'file_name' => '薪资管理制度',
                'status_text' => '生效中',
            ],
            [
                'id' => 4,
                'file_name' => '请假管理制度',
                'status_text' => '已失效',
            ],
            [
                'id' => 5,
                'file_name' => '绩效考核制度',
                'status_text' => '生效中',
            ],
            [
                'id' => 6,
                'file_name' => '安全管理制度',
                'status_text' => '生效中',
            ],
            [
                'id' => 7,
                'file_name' => '培训管理制度',
                'status_text' => '已失效',
            ],
            [
                'id' => 8,
                'file_name' => '保密管理制度',
                'status_text' => '生效中',
            ],
            [
                'id' => 9,
                'file_name' => '差旅管理制度',
                'status_text' => '生效中',
            ],
            [
                'id' => 10,
                'file_name' => '奖惩管理制度',
                'status_text' => '已失效',
            ]
        ];
        
        return response()->json([
            'code' => 0,
            'msg' => '规章制度数据获取成功',
            'data' => $regulationsData
        ]);
    }

    // 获取通知
    public function getNoticeStats()
    {
        $noticeData = [
            [
                'id' => 1,
                'file_name' => '关于加强春节期间安全管理的通知',
                'status' => 1,
                'status_text' => '已生效',
                'content' => '各部门务必做好春节期间的安全防护工作',
                'publish_date' => '2025-01-15',
                'effective_date' => '2025-01-20',
                'department' => '行政部'
            ],
            [
                'id' => 2,
                'file_name' => '员工健康体检安排通知',
                'status' => 1,
                'status_text' => '已生效',
                'content' => '2025年度员工健康体检安排已确定',
                'publish_date' => '2025-01-10',
                'effective_date' => '2025-01-15',
                'department' => '人事部'
            ],
            [
                'id' => 3,
                'file_name' => '新版考勤管理制度执行通知',
                'status' => 1,
                'status_text' => '已生效',
                'content' => '新版考勤管理制度将于本月开始执行',
                'publish_date' => '2025-01-08',
                'effective_date' => '2025-01-12',
                'department' => '人事部'
            ],
            [
                'id' => 4,
                'file_name' => '办公设备更新计划通知',
                'status' => 0,
                'status_text' => '未生效',
                'content' => '计划更新部分老旧办公设备',
                'publish_date' => '2025-01-05',
                'effective_date' => '2025-02-01',
                'department' => '行政部'
            ],
            [
                'id' => 5,
                'file_name' => '年终奖发放标准公示',
                'status' => 1,
                'status_text' => '已生效',
                'content' => '2024年度年终奖发放标准已确定',
                'publish_date' => '2025-01-03',
                'effective_date' => '2025-01-05',
                'department' => '财务部'
            ],
            [
                'id' => 6,
                'file_name' => '员工培训计划通知',
                'status' => 1,
                'status_text' => '已生效',
                'content' => '第一季度员工培训计划已制定',
                'publish_date' => '2024-12-28',
                'effective_date' => '2025-01-01',
                'department' => '人事部'
            ],
            [
                'id' => 7,
                'file_name' => '春节放假安排通知',
                'status' => 1,
                'status_text' => '已生效',
                'content' => '根据国家法定节假日安排',
                'publish_date' => '2024-12-25',
                'effective_date' => '2024-12-26',
                'department' => '行政部'
            ]
        ];
        
        return response()->json([
            'code' => 0,
            'msg' => '通知数据获取成功',
            'data' => $noticeData
        ]);
    }
}